<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Specifying the metadata</title>

 </head>
 <body><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="sdodasrel.examples-crud.html">Creating, retrieving, updating and deleting data</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="sdodasrel.examples.one-table.html">One-table examples</a></div>
 <div class="up"><a href="sdodasrel.examples.html">范例</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="sdodasrel.metadata" class="section">
  <h2 class="title">Specifying the metadata</h2>
  <p class="para">
   This first long section describes in detail how the metadata describing 
   the database and the required SDO model is supplied to the 
   Relational DAS.
  </p>
  <p class="para">
   When the constructor for the Relational DAS is invoked, it needs to be 
   passed several pieces of information. The bulk of the information, 
   passed as an associative array in the first argument to the constructor, 
   tells the Relational DAS what it needs to know about the relational 
   database. It describes the names of the tables, columns, primary keys 
   and foreign keys. It should be fairly easy to understand what is 
   required, and once written it can be placed in a php file and included 
   when needed. The remainder of the information, passed in the second 
   and third arguments to the constructor, tells the Relational DAS what 
   it needs to know about the relationships between objects and the shape 
   of the data graph; it ultimately determines how the data from the 
   database is to be normalized into a graph.
  </p>

  <div class="section" id="sdodasrel.metadata.database">
   <h2 class="title">Database metadata</h2>
   <p class="para">
    The first argument to the constructor describes the target 
    relational database.
   </p>

   <p class="para">
    Each table is described by an associative array with up to four keys.
    <table class="doctable informaltable">
     
      <thead>
       <tr>
        <th>Key</th>
        <th>Value</th>
       </tr>

      </thead>

      <tbody class="tbody">
       <tr>
        <td>name</td>
        <td>The name of the table.</td>
       </tr>

       <tr>
        <td>columns</td>
        <td>
         An array listing the names of the columns, in any order.
        </td>
       </tr>

       <tr>
        <td>PK</td>
        <td>The name of the column containing the primary key.</td>
       </tr>

       <tr>
        <td>FK</td>
        <td>An array with two entries, &#039;from&#039; and &#039;to&#039;, which define 
         a column containing a foreign key, and a table to which the foreign
         key points. If there are no foreign keys in the table then the 
         &#039;FK&#039; entry does not need to be specified. Only one foreign key 
         can be specified. Only a foreign key pointing to the primary key 
         of a table can be specified.
        </td>
       </tr>

      </tbody>
     
    </table>

   </p>

   <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br /></span><span style="color: #FF8000">/*****************************************************************<br />*&nbsp;METADATA&nbsp;DEFINING&nbsp;THE&nbsp;DATABASE<br />******************************************************************/<br /></span><span style="color: #0000BB">$company_table&nbsp;</span><span style="color: #007700">=&nbsp;array&nbsp;(<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'name'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'company'</span><span style="color: #007700">,<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'columns'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;array(</span><span style="color: #DD0000">'id'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'name'</span><span style="color: #007700">,&nbsp;&nbsp;</span><span style="color: #DD0000">'employee_of_the_month'</span><span style="color: #007700">),<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'PK'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'id'</span><span style="color: #007700">,<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'FK'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;array&nbsp;(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">'from'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'employee_of_the_month'</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">'to'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'employee'</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),<br />&nbsp;&nbsp;);<br /></span><span style="color: #0000BB">$department_table&nbsp;</span><span style="color: #007700">=&nbsp;array&nbsp;(<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'name'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'department'</span><span style="color: #007700">,&nbsp;<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'columns'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;array(</span><span style="color: #DD0000">'id'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'name'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'location'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'number'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'co_id'</span><span style="color: #007700">),<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'PK'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'id'</span><span style="color: #007700">,<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'FK'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;array&nbsp;(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">'from'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'co_id'</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">'to'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'company'</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)<br />&nbsp;&nbsp;);<br /></span><span style="color: #0000BB">$employee_table&nbsp;</span><span style="color: #007700">=&nbsp;array&nbsp;(<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'name'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'employee'</span><span style="color: #007700">,<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'columns'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;array(</span><span style="color: #DD0000">'id'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'name'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'SN'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'manager'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'dept_id'</span><span style="color: #007700">),<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'PK'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'id'</span><span style="color: #007700">,<br />&nbsp;&nbsp;</span><span style="color: #DD0000">'FK'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;array&nbsp;(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">'from'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'dept_id'</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">'to'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'department'</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)<br />&nbsp;&nbsp;);<br /></span><span style="color: #0000BB">$database_metadata&nbsp;</span><span style="color: #007700">=&nbsp;array(</span><span style="color: #0000BB">$company_table</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$department_table</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$employee_table</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
   </div>


   <p class="para">
    This metadata corresponds to a relational database that might have 
    been defined to MySQL as:
   </p>
   <div class="example-contents">
<div class="sqlcode"><pre class="sqlcode">create table company (
 id integer auto_increment,
 name char(20),
 employee_of_the_month integer,
 primary key(id)
);
create table department (
 id integer auto_increment,
 name char(20),
 location char(10),
 number integer(3),
 co_id integer,
 primary key(id)
);
create table employee (
 id integer auto_increment,
 name char(20),
 SN char(4),
 manager tinyint(1),
 dept_id integer,
 primary key(id)
);</pre>
</div>
   </div>

   <p class="para">
   or to DB2 as:
   </p>
   <div class="example-contents">
<div class="sqlcode"><pre class="sqlcode">create table company ( \
  id integer not null generated by default as identity,  \
  name varchar(20), \
  employee_of_the_month integer, \
  primary key(id) )
create table department ( \
  id integer not null generated by default as identity, \
  name varchar(20), \
  location varchar(10), \
  number integer, \
  co_id integer, \
  primary key(id) )
create table employee ( \
  id integer not null generated by default as identity, \
  name varchar(20), \
  SN char(4), \
  manager smallint, \
  dept_id integer, \
  primary key(id) )</pre>
</div>
   </div>

   
   <p class="para">
    Note that although in this example there are no foreign keys specified 
    to the database and so the database is not expected to enforce 
    referential integrity, the intention behind the
    <var class="varname"><var class="varname">co_id</var></var>
    column on the department table and the
    <var class="varname"><var class="varname">dept_id</var></var>
    column on the employee table is they should contain the primary key 
    of their containing company or department record, respectively.
    So these two columns are acting as foreign keys.
   </p>
                  
   <p class="para">
    There is a third foreign key in this example, that from the
    <var class="varname"><var class="varname">employee_of_the_month</var></var>
    column of the company record to a single row of the employee table.
    Note the difference in intent between this foreign key and the other 
    two. The
    <var class="varname"><var class="varname">employee_of_the_month</var></var>
    column represents a single-valued relationship: there can be only 
    one employee of the month for a given company.
    The
    <var class="varname"><var class="varname">co_id</var></var>
    and
    <var class="varname"><var class="varname">dept_id</var></var>
    columns represent multi-valued relationships: a company can contain 
    many departments and a department can contain many employees.
    This distinction will become evident when the remainder of the metadata 
    picks out the company-department and department-employee relationships 
    as containment relationships.
   </p>

   <p class="para">
    There are a few simple rules to be followed when constructing the 
    database metadata:
   </p>

   <ul class="itemizedlist">
    <li class="listitem">
     <p class="para">
      All tables must have primary keys, and the primary keys must be
      specified in the metadata. Without primary keys it is not possible 
      to keep track of object identities. As you can see from the SQL 
      statements that create the tables, primary keys can be 
      auto-generated, that is, generated and assigned by the database when
      a record is inserted. In this case the auto-generated primary key 
      is obtained from the database and inserted into the data object 
      immediately after the row is inserted into the database.
     </p>
    </li>

    <li class="listitem">
     <p class="para">
      It is not necessary to specify in the metadata all the columns 
      that exist in the database, only those that will be used. 
      For example, if the company table had another column that the 
      application did not want to access with SDO, this need not be 
      specified in the metadata. On the other hand it would have done 
      no harm to specify it: if specified in the metadata but never 
      retrieved, or assigned to by the application, then the unused column 
      will not affect anything.
     </p>
    </li>

    <li class="listitem">
     <p class="para">
      In the database metadata note that the foreign key definitions 
      identify not the destination column in the table which is pointed 
      to, but the table name itself. Strictly, the relational model 
      permits the destination of a foreign key to be a non-primary key.
      Only foreign keys that point to a primary key are useful for 
      constructing the SDO model, so the metadata specifies the table name.
      It is understood that the foreign key points to the primary key of 
      the given table.
     </p>
    </li>
   </ul>

   <p class="para">
    Given these rules, and given the SQL statements that define the 
    database, the database metadata should be easy to construct.
   </p>

   <div class="section" id="sdodasrel.metadata.database.model">
    <h2 class="title">What the Relational DAS does with the metadata</h2>
    <p class="para">
     The Relational DAS uses the database metadata to form most of the 
     SDO model. For each table in the database metadata, an SDO type 
     is defined. Each column which can represent a primitive value 
     (columns which are not defined as foreign keys) are added 
     as properties to the SDO type.
    </p>
    <p class="para">
     All primitive properties are given a type of string in the SDO model, 
     regardless of their SQL type. When writing values back to the 
     database the Relational DAS will create SQL statements that treat 
     the values as strings, and the database will convert them to the 
     appropriate type.
    </p>

    <p class="para">
     Foreign keys are interpreted in one of two ways, depending on the 
     metadata in the third argument to the constructor that defines
     the SDO containment relationships.
     A discussion of this is therefore deferred until the section on
     <a href="sdodasrel.metadata.html#sdodasrel.metadata.crels" class="link">
      SDO containment relationships
     </a>
     below.
    </p>
   </div>

   <div class="section" id="sdodasrel.metadata.approottype">
    <h2 class="title">Specifying the application root type</h2>
    <p class="para">
     The second argument to the constructor is the application root type.
     The true root of each data graph is an object of a special root type 
     and all application data objects come somewhere below that. Of the 
     various application types in the SDO model, one has to be the 
     application type immediately below the root of the data graph.
     If there is only one table in the database metadata, the application 
     root type can be inferred, and this argument can be omitted.
    </p>
   </div>

   <div class="section" id="sdodasrel.metadata.crels">
    <h2 class="title">Specifying the SDO containment relationships</h2>

    <p class="para">
     The third argument to the constructor defines how the types in the 
     model are to be linked together to form a graph. It identifies the 
     parent-child relationships between the types which collectively form a 
     graph. The relationships need to be supported by foreign keys to be 
     found in the data, in a way shortly to be described.
    </p>

    <p class="para">
     The metadata is an array containing one or more associative arrays, 
     each of which identifies a parent and a child. The example below shows 
     a parent-child relationship from company to department, and another 
     from department to employee. Each of these will become an SDO property 
     defining a multi-valued containment relationship in the SDO model.

    </p>
    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br />$department_containment&nbsp;</span><span style="color: #007700">=&nbsp;array(&nbsp;</span><span style="color: #DD0000">'parent'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'company'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'child'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'department'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$employee_containment&nbsp;</span><span style="color: #007700">=&nbsp;array(&nbsp;</span><span style="color: #DD0000">'parent'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'department'</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">'child'&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #DD0000">'employee'</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$SDO_containment_metadata&nbsp;</span><span style="color: #007700">=&nbsp;array(</span><span style="color: #0000BB">$department_containment</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$employee_containment</span><span style="color: #007700">);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>


    <p class="para">
     Foreign keys in the database metadata are interpreted as properties 
     with either multi-valued containment relationships or single-valued 
     non-containment references, depending on whether they have a 
     corresponding SDO containment relationship specified in the metadata. 
     In the example here, the foreign keys from department to company (the 
     <var class="varname"><var class="varname">co_id</var></var>
     column in the department table)
     and from employee to department (the
     <var class="varname"><var class="varname">dept_id</var></var>
     column in the employee table) are interpreted as supporting the
     SDO containment relationships.
     Each containment relationship mentioned in the SDO containment relationships
     metadata must have a corresponding foreign key present in the
     database and defined in the database metadata. The values of the 
     foreign key columns for containment relationships do not appear in the 
     data objects, instead each is represented by a containment relationship 
     from the parent to the child. So the
     <var class="varname"><var class="varname">co_id</var></var>
     column in the department row in the database, for example, does not 
     appear as a property on the department type, but instead as a 
     containment relationship called
     <var class="varname"><var class="varname">department</var></var>
     on the company type.
     Note that the foreign key and the parent-child relationship appear to 
     have opposite senses: the foreign key points from the department to 
     the company, but the parent-child relationship points from company to 
     department.
    </p>

    <p class="para">
     The third foreign key in this example, the
     <var class="varname"><var class="varname">employee_of_the_month</var></var>
     ,
     is handled differently.
     This is not mentioned in the SDO containment relationships metadata.
     As a consequence this is interpreted in the second way: it becomes 
     a single-valued non-containment reference on the company object, to
     which can be assigned references to SDO data objects of the employee 
     type. It does appear as a property on the company type. The way to 
     assign a value to it in the SDO data graph is to have a graph that 
     contains an employee object through the containment relationships, and 
     to assign the object to it. This is illustrated in the later examples 
     below.
    </p>
   </div>
  </div> 
 </div><hr /><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="sdodasrel.examples-crud.html">Creating, retrieving, updating and deleting data</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="sdodasrel.examples.one-table.html">One-table examples</a></div>
 <div class="up"><a href="sdodasrel.examples.html">范例</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
